﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace Ivy.Common.Framework.DataAccess
{
    public enum DataProviderTypes
    {
        SqlServer = 0,
        Oracle = 1,
        OleDb = 2
    }
    
    public static class DbDataTypes
    {
        public const string Int = "int";
        public const string Bit = "bit";
        public const string DatetTime = "datetime";
        public const string VarChar = "varchar";
    }
    public static class DbTableSchemaNames
    {
        public const string ColumnName = "ColumnName";
        public const string ColumnOrdinal = "ColumnOrdinal";
        public const string ColumnSize = "ColumnSize";
        public const string NumericPrecision = "NumericPrecision";
        public const string NumericScale = "NumericScale";
        public const string IsUnique = "IsUnique";
        public const string IsKey = "IsKey";
        public const string BaseServerName = "BaseServerName";
        public const string BaseCatalogName = "BaseCatalogName";
        public const string BaseColumnName = "BaseColumnName";
        public const string BaseSchemaName = "BaseSchemaName";
        public const string BaseTableName = "BaseTableName";
        public const string DataType = "DataType";
        public const string AllowDBNull = "AllowDBNull";
        public const string ProviderType = "ProviderType";
        public const string IsAliased = "IsAliased";
        public const string IsExpression = "IsExpression";
        public const string IsIdentity = "IsIdentity";
        public const string IsAutoIncrement = "IsAutoIncrement";
        public const string IsRowVersion = "IsRowVersion";
        public const string IsHidden = "IsHidden";
        public const string IsLong = "IsLong";
        public const string IsReadOnly = "IsReadOnly";
        public const string ProviderSpecificDataType = "ProviderSpecificDataType";
        public const string DataTypeName = "DataTypeName";
        public const string XmlSchemaCollectionDatabase = "XmlSchemaCollectionDatabase";
        public const string XmlSchemaCollectionOwningSchema = "XmlSchemaCollectionOwningSchema";
        public const string XmlSchemaCollectionName = "XmlSchemaCollectionName";
        public const string UdtAssemblyQualifiedName = "UdtAssemblyQualifiedName";
        public const string NonVersionedProviderType = "NonVersionedProviderType";
        public const string IsColumnSet = "IsColumnSet";

    }
    public class DataAccessProviderUtility
    {
        #region Variables & Constants
        const string _DefaultConnectionStringName = "DefaultConnectionStringName";
        const string _DefaultConfigKeyForDataProvider = "DataProvider";
        const string _DefaultConfigKeyForConnectionString = "ConnectionString";
        const string _SqlServerDataProvider = "System.Data.SqlClient";
        const string _OleDbDataProvider = "System.Data.OledbClient";
        const string _OracleDataProvider = "System.Data.OracleClient";
        #endregion Variables & Constants

        #region Constructor & Destructor
        #endregion Constructor & Destructor

        #region Public Properties
        #endregion Public Properties

        #region Public Methods

        /// <summary>
        /// Help to create instance DbProviderFactory object from App.Config settings.
        /// make sure that App.Config has a entry for [DefaultConnectionStringName] under '<appSettings>' section
        /// '<add key="DefaultConnectionStringName" value="XXXX"/>'
        /// and a corresponding entry for the respective value 'XXXX' under '<connectionStrings>' section
        /// '<add name="XXXX" connectionString="----------;" providerName="-------" />'
        /// </summary>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory()
        {
            AppSettingsReader _AppSettingsReader = new AppSettingsReader();
            string _ConnectionStringName = _AppSettingsReader.GetValue(_DefaultConnectionStringName, typeof(string)).ToString();
            if (_ConnectionStringName == "LocalSystem")
            {
                _ConnectionStringName = System.Environment.MachineName + "_SqlDBConnection";
            }
            ConnectionStringSettings _ConnectionStringSettings = ConfigurationManager.ConnectionStrings[_ConnectionStringName];
            string _DataProvider = _ConnectionStringSettings.ProviderName;
            return DbProviderFactories.GetFactory(_DataProvider);
        }

        /// <summary>
        /// Help to create instance DbProviderFactory object from App.Config settings.
        /// make sure that App.Config has a entry for [DataProvider] under '<appSettings>' section 
        /// '<add key="AAAAA" value="XXXX"/>'
        /// For example
        /// '<add key="MyDataProvider" value="System.Data.SqlClient"/>'
        /// and pass it as parameter
        /// </summary>
        /// <param name="AppSettingsConfigKeyForDataProvider"></param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory(string AppSettingsConfigKeyForDataProvider)
        {
            AppSettingsReader _AppSettingsReader = new AppSettingsReader();
            string _DataProvider = _AppSettingsReader.GetValue(AppSettingsConfigKeyForDataProvider, typeof(string)).ToString();
            //string _DataProvider = ConfigurationSettings.AppSettings[AppSettingsConfigKeyForDataProvider].ToString();
            return DbProviderFactories.GetFactory(_DataProvider);
        }
        public static DbProviderFactory GetDbProviderFactory(DataProviderTypes DataProviderType)
        {
            string _DataProvider = string.Empty;
            if (DataProviderType == DataProviderTypes.SqlServer)
            {
                _DataProvider = _SqlServerDataProvider;
            }
            if (DataProviderType == DataProviderTypes.Oracle)
            {
                _DataProvider = _OracleDataProvider;
            }
            if (DataProviderType == DataProviderTypes.OleDb)
            {
                _DataProvider = _OleDbDataProvider;
            }
            return DbProviderFactories.GetFactory(_DataProvider);
        }
        
        /// <summary>
        /// Creates an instance of DbConnection object with a specific type of data provider,
        /// without connection string.
        /// make sure that App.Config has a entry for [DefaultConnectionStringName] under '<appSettings>' section
        /// '<add key="DefaultConnectionStringName" value="XXXX"/>'
        /// and a corresponding entry for the respective value 'XXXX' under '<connectionStrings>' section
        /// '<add name="XXXX" connectionString="----------;" providerName="-------" />'
        /// </summary>
        /// <returns></returns>
        public static DbConnection CreateConnection()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateConnection();
        }
        
        /// <summary>
        /// Creates an instance of DbConnection object with a specific type of data provider,
        /// without connection string.
        /// make sure that App.Config has a entry for [DataProvider] under '<appSettings>' section 
        /// '<add key="AAAAA" value="XXXX"/>'
        /// For example
        /// '<add key="MyDataProvider" value="System.Data.SqlClient"/>'
        /// and pass it as parameter
        /// </summary>
        /// <param name="AppSettingsConfigKeyForDataProvider"></param>
        /// <returns></returns>
        public static DbConnection CreateConnection(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateConnection();
        }
        public static DbConnection CreateConnection(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateConnection();
        }
        
        /// <summary>
        /// Help to create instance DbConnection object from App.Config settings.
        /// make sure that App.Config has a entry for [DefaultConnectionStringName] under '<appSettings>' section
        /// '<add key="DefaultConnectionStringName" value="XXXX"/>'
        /// and a corresponding entry for the respective value 'XXXX' under '<connectionStrings>' section
        /// '<add name="XXXX" connectionString="----------;" providerName="-------" />'
        /// </summary>
        /// <returns></returns>
        public static DbConnection CreateConnectionWithConnectionString()
        {
            AppSettingsReader _AppSettingsReader = new AppSettingsReader();
            string _ConnectionStringName = _AppSettingsReader.GetValue(_DefaultConnectionStringName, typeof(string)).ToString();
            if (_ConnectionStringName == "LocalSystem")
            {
                _ConnectionStringName = System.Environment.MachineName + "_SqlDBConnection";
            }
            ConnectionStringSettings _ConnectionStringSettings = ConfigurationManager.ConnectionStrings[_ConnectionStringName];
            string _ConnectionString = _ConnectionStringSettings.ConnectionString;
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            DbConnection _DbConnection = _DbProviderFactory.CreateConnection();
            _DbConnection.ConnectionString = _ConnectionString;
            return _DbConnection;
        }

        /// <summary>
        /// [AppSettingsConfigKeyForDataProvider] 
        /// Help to create instance DbProviderFactory object from App.Config settings.
        /// make sure that App.Config has a entry for DataProvider under '<appSettings>' section 
        /// '<add key="AAAAA" value="XXXX"/>'
        /// For example
        /// '<add key="MyDataProvider" value="System.Data.SqlClient"/>'
        /// and pass it as parameter.
        /// 
        /// [AppSettingsConfigKeyForConnectionString]
        /// Help to create instance DbConnection object from App.Config settings.
        /// make sure that App.Config has a entry for ConnectionString under '<appSettings>' section
        /// '<add key="AAAAA" value="XXXX"/>'
        /// For Example
        /// '<add name="MyConnectionString" connectionString="Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=IvyDB;Integrated Security=True;" providerName="System.Data.SqlClient" />'
        /// and pass it as parameter.
        /// </summary>
        /// <param name="AppSettingsConfigKeyForDataProvider"></param>
        /// <param name="AppSettingsConfigKeyForConnectionString"></param>
        /// <returns></returns>
        public static DbConnection CreateConnectionWithConnectionString(string AppSettingsConfigKeyForDataProvider, string AppSettingsConfigKeyForConnectionString)
        {
            AppSettingsReader _AppSettingsReader = new AppSettingsReader();
            string _ConnectionString = _AppSettingsReader.GetValue(AppSettingsConfigKeyForConnectionString, typeof(string)).ToString();
            //string _ConnectionString = ConfigurationSettings.AppSettings[ConfigKeyForConnectionString].ToString();
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            DbConnection _DbConnection = _DbProviderFactory.CreateConnection();
            _DbConnection.ConnectionString = _ConnectionString;
            return _DbConnection;
        }
        public static DbConnection CreateConnectionWithConnectionString(DataProviderTypes DataProviderType, string ConnectionString)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            DbConnection _DbConnection = _DbProviderFactory.CreateConnection();
            _DbConnection.ConnectionString = ConnectionString;
            return _DbConnection;
        }
        public static DbCommand CreateCommand()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateCommand();
        }
        public static DbCommand CreateCommand(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateCommand();
        }
        public static DbCommand CreateCommand(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateCommand();
        }
        public static DbParameter CreateParameter()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateParameter();
        }
        public static DbParameter CreateParameter(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateParameter();
        }
        public static DbParameter CreateParameter(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateParameter();
        }
        public static DbCommandBuilder CreateCommandBuilder()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateCommandBuilder();
        }
        public static DbCommandBuilder CreateCommandBuilder(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateCommandBuilder();
        }
        public static DbCommandBuilder CreateCommandBuilder(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateCommandBuilder();
        }
        public static DbConnectionStringBuilder CreateConnectionStringBuilder()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateConnectionStringBuilder();
        }
        public static DbConnectionStringBuilder CreateConnectionStringBuilder(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateConnectionStringBuilder();
        }
        public static DbConnectionStringBuilder CreateConnectionStringBuilder(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateConnectionStringBuilder();
        }
        public static DbDataAdapter CreateDataAdapter()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateDataAdapter();
        }
        public static DbDataAdapter CreateDataAdapter(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateDataAdapter();
        }
        public static DbDataAdapter CreateDataAdapter(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateDataAdapter();
        }
        public static DbDataSourceEnumerator CreateDataSourceEnumerator()
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory();
            return _DbProviderFactory.CreateDataSourceEnumerator();
        }
        public static DbDataSourceEnumerator CreateDataSourceEnumerator(string AppSettingsConfigKeyForDataProvider)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(AppSettingsConfigKeyForDataProvider);
            return _DbProviderFactory.CreateDataSourceEnumerator();
        }
        public static DbDataSourceEnumerator CreateDataSourceEnumerator(DataProviderTypes DataProviderType)
        {
            DbProviderFactory _DbProviderFactory = GetDbProviderFactory(DataProviderType);
            return _DbProviderFactory.CreateDataSourceEnumerator();
        }
        public static void AddParameter(DbCommand Command, string ParameterName, ParameterDirection ParamDirection, DbType DataType, object ParameterValue)
        {
            DbParameter _DbParameter = Command.CreateParameter();
            _DbParameter.ParameterName = ParameterName;
            _DbParameter.Direction = ParamDirection;
            _DbParameter.DbType = DataType;
            if (DataType == DbType.String && ParameterValue.ToString() == string.Empty)
            {
                _DbParameter.Value = DBNull.Value;
            }
            else
            {
                _DbParameter.Value = ParameterValue;
            }
            Command.Parameters.Add(_DbParameter);
        }
        public static void AddParameter(DbCommand Command, DbParameter Parameter)
        {
            DbParameter _DbParameter = Command.CreateParameter();
            if (_DbParameter.GetType() == Parameter.GetType())
            {
                Command.Parameters.Add(Parameter);
            }
            else
            {
                throw new Exception("Invalid Parameter! Provider for the Command object and Parameter object are not matching");
            }
        }
        public static void RemoveParameter(DbCommand Command, string ParameterName, DbType DataType, object ParameterValue)
        {
            DbParameter _DbParameter = Command.CreateParameter();
            _DbParameter.ParameterName = ParameterName;
            _DbParameter.DbType = DataType;
            _DbParameter.Value = ParameterValue;
            Command.Parameters.Remove(_DbParameter);
        }
        public static void RemoveParameter(DbCommand Command, DbParameter Parameter)
        {
            DbParameter _DbParameter = Command.CreateParameter();
            if (_DbParameter.GetType() == Parameter.GetType())
            {
                Command.Parameters.Remove(Parameter);
            }
            else
            {
                throw new Exception("Invalid Parameter! Provider for the Command object and Parameter object are not matching");
            }
        }
        public static List<string> GetTableList(DbConnection pDbConnection)
        {
            List<string> lListOfTables = new List<string>();
            try
            {
                if (pDbConnection.State == ConnectionState.Open)
                {
                    DataTable lTableDatabaseList = pDbConnection.GetSchema("Tables");
                    foreach (DataRow lDataRow in lTableDatabaseList.Rows)
                    {
                        lListOfTables.Add(lDataRow["TABLE_NAME"].ToString());
                    }
                }
                else
                {
                    throw new Exception("[pDbConnection] is not in [Open] state");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return lListOfTables;
        }
        public static List<DataRow> GetTableColumnList(DbConnection pDbConnection, string pTableName)
        {
            List<DataRow> lListOfColumns = new List<DataRow>();
            try
            {
                //DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                //DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                DbCommand CmdObj = pDbConnection.CreateCommand();
                //CmdObj.CommandText = "SELECT * FROM [@SelectedTableName] WHERE 1=2";
                CmdObj.CommandText = "SELECT * FROM [" + pTableName + "] WHERE 1=2";
                CmdObj.CommandType = CommandType.Text;

                //DbParameter DbParamObj = DataAccessProviderUtility.CreateParameter();

                //DbParamObj.ParameterName = "@SelectedTableName";
                //DbParamObj.Direction = ParameterDirection.Input;
                //DbParamObj.DbType = DbType.String;
                //DbParamObj.Value = pTableName;
                //DataAccessProviderUtility.AddParameter(CmdObj, DbParamObj);

                //DataAccessProviderUtility.AddParameter(CmdObj, "@SelectedTableName", ParameterDirection.Input, DbType.String, pTableName);

                CmdObj.Connection = pDbConnection;
                if (pDbConnection.State != ConnectionState.Open)
                {
                    pDbConnection.Open();
                }
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                _DataReader.Read();

                DataTable lTableColList = _DataReader.GetSchemaTable();
                foreach (DataRow lDataRow in lTableColList.Rows)
                {
                    lListOfColumns.Add(lDataRow);
                }
                CmdObj.Dispose();
                pDbConnection.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return lListOfColumns;
        }
        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        #endregion Private Methods
    }
}
